package x7.heatctrl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.concurrent.Callable;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSource;

/**
 * DB-Helper-File
 *
 * @author simon
 *
 */
public class DB {
	
    private static Map<String,DB> instances=new HashMap<String, DB>();

    private String id;
    private boolean useBatch;
    private boolean escape=false;
    private DataSource datasource;
    public static boolean DEBUG = System.getProperty("db.debug", "true").toLowerCase().startsWith("t");

    // constructor
    private DB(String id) {
        this.id = id;
    }
    
    // constructor
    public DB(DataSource ds) {
    	this.datasource = ds;
    }
    
    public DB(String id, String url, String user, String passwd) {
        this.id = id;
        System.setProperty("db."+id+".url", url);
        System.setProperty("db."+id+".user", user);
        System.setProperty("db."+id+".password", passwd);
    }


    /**
     * returns the DB for the given id.
     *
     * <p>
     *   The Configuration for the DB will be taken from a all files found in /WEB-INF/etc.
     *   So just put a file with your configuration in there like /WEB-INF/etc/system.properties.
     * </p>
     * <p>
     *   The File should contain the following format:
     *   <p><pre>db.<b>DBID</b>.key = value</pre></p>
     *
     *   <pre>
     *     db.artikel.url      = jdbc:mysql://localhost/artikel
     *     db.artikel.user     = root
     *     db.artikel.password = mysql
     *     db.artikel.driver   = com.mysql.jdbc.Driver
     *     ...
     *   </pre>
     *
     *   using "artikel" as id would return the database as configured above.
     *
     * </p>
     * @param id of the DB
     * @return the cached db-Object
     */
    public static DB getDb(String id) {
        DB db = instances.get(id);
        if (db==null) {
            db=new DB(id);
            instances.put(id, db);
        }
        return db;
    }

    // returns a Property from System.conf
    private String getProperty(String key,String def) {
        String val = System.getProperty("db."+id+"."+key);
        if (val==null)
            val = System.getProperty("db."+key);
        return val==null?def:val;
    }

    // inits the datasource
    private void init() {
        if (datasource==null) {
        	BasicDataSource ds = new BasicDataSource();
        	datasource = ds;
            ds.setDriverClassName(getProperty("driver", "com.mysql.jdbc.Driver"));
            ds.setUrl(getProperty("url","jdbc:mysql://vm/opengeodb"));
            ds.setUsername(getProperty("user",null));
            ds.setPassword(getProperty("password",null));
            ds.setMinEvictableIdleTimeMillis(1800000);
            ds.setTimeBetweenEvictionRunsMillis(1800000);
            ds.setNumTestsPerEvictionRun(3);
            boolean isMysql=ds.getDriverClassName().contains("mysql");
            ds.setTestOnBorrow(isMysql);
            ds.setTestWhileIdle(true);
            ds.setTestOnReturn(true);
            ds.setMaxActive(Integer.parseInt(getProperty("maxActive","10")));
            ds.setMaxIdle(Integer.parseInt(getProperty("maxIdle","10")));
            escape=getProperty("escape","false").startsWith("t");
            if (isMysql) ds.setValidationQuery("select 1");
        }
    }

    /**
     * returns a connection from the datasource.
     * This Connection must (in case of a transaction) be release by calling close(Connection);
     * @return
     */
    public Connection getConnection() {
        init();
        try {
            return datasource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    private String replaceParams(String sql, Object ... args) {
    	String s = sql;
    	if (args!=null) {
    	for (Object o:args) {
    		int p=s.indexOf("?");
    		if (p>0)
    			s=s.substring(0,p)+o+s.substring(p+1);
    	}
    	}
    	return s;

    }

    /**
     * executes a Update-Query and returns the number of rows changed.
     * @param sql
     * @param values
     * @return
     */
    public int executeUpdate(String sql, Object ... values) {
        if (DEBUG) System.out.println("update: "+replaceParams(sql, values));
        final Connection con = getConnection();
        try {
            if (values==null  | values.length==0) {
                Statement st = con.createStatement();
                final int val = st.executeUpdate(sql);
                close(st);
                if (DEBUG) System.out.println("                modified => "+val);
                return val;
            }
            else {
                PreparedStatement st = prepareStatement(con,sql);
                setValuesInStatement(st, values);
                if (useBatch) {
                	st.addBatch();
                	return -1;
                }
                else {
	                final int val=st.executeUpdate();
	                close(st);
	                if (DEBUG) System.out.println("                modified => "+val);
	                return val;
                }

            }
        } catch (SQLException ex) {
        	StringBuilder msg = new StringBuilder("Error with query : ").append(sql);
            for (int i=0;i<values.length;i++) {
                msg.append("\n  value "+i+": "+values[i]);
            }
            throw new RuntimeException(msg.toString()+" : "+ex.toString(),ex);
        } finally {
            close(con);
        }
    }

    /**
     * updaztes the given values in the table
     * @param table the table
     * @param pkColumn the columns of the pk
     * @param pk the pk
     * @param values the values as key/value/pair [key,value,key,value]
     * @return the number of modified rows (should be 1)
     */
    public int insert(String table, Object ...values ) {
    	StringBuilder sb = new StringBuilder("insert ").append(table);
    	Object[] args=new Object[values.length/2+1];
    	for (int i=0;i<values.length;i+=2) {
    		sb.append((i==0)?"(":", ");
    		sb.append(values[i]);
    		args[i/2]=values[i+1];
    	}
    	sb.append(") values (?");
    	for (int i=2;i<values.length;i+=2) sb.append(",?");
    	return executeUpdate(sb.toString(), args);
    }
    /**
     * updaztes the given values in the table
     * @param table the table
     * @param pkColumn the columns of the pk
     * @param pk the pk
     * @param values the values as key/value/pair [key,value,key,value]
     * @return the number of modified rows (should be 1)
     */
    public Object insertWithPK(String table, Object ...values ) {
    	StringBuilder sb = new StringBuilder("insert ").append(table);
    	Object[] args=new Object[values.length/2+1];
    	for (int i=0;i<values.length;i+=2) {
    		sb.append((i==0)?"(":", ");
    		sb.append(values[i]);
    		args[i/2]=values[i+1];
    	}
    	sb.append(") values (?");
    	for (int i=2;i<values.length;i+=2) sb.append(",?");
    	return executeInsert(sb.toString(), args);
    }


    /**
     * updaztes the given values in the table
     * @param table the table
     * @param pkColumn the columns of the pk
     * @param pk the pk
     * @param values the values as key/value/pair [key,value,key,value]
     * @return the number of modified rows (should be 1)
     */
    public int update(String table, String pkColumn, Object pk, Object ...values ) {
    	StringBuilder sb = new StringBuilder("update ").append(table);
    	Object[] args=new Object[values.length/2+1];
    	for (int i=0;i<values.length;i+=2) {
    		sb.append((i==0)?" set ":", ");
    		sb.append(values[i]).append("=?");
    		args[i/2]=values[i+1];
    	}
    	args[args.length-1]=pk;
    	sb.append(" where ").append(pkColumn).append("=?");
    	return executeUpdate(sb.toString(), args);
    }
    /**
     * executes a instert-Query and returns the autogenerated value.
     * @param sql
     * @param values
     * @return
     */
    public Object executeInsert(String sql, Object ... values) {
        if (DEBUG) {
        	System.out.println("insert "+sql);
            for (Object o : values) System.out.print("{ "+o+" }");
            System.out.println();
        }
        Object key=null;
        final Connection con = getConnection();
        try {
            if (values==null  | values.length==0) {
                Statement st = con.createStatement();
                final int val = st.executeUpdate(sql,Statement.RETURN_GENERATED_KEYS);
                ResultSet rs = st.getGeneratedKeys();
                if (rs.next()) key=rs.getObject(1);
                rs.close();
                st.close();
                return key;
            }
            else {
                PreparedStatement st = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                setValuesInStatement(st, values);
                final int val=st.executeUpdate();
                ResultSet rs = st.getGeneratedKeys();
                if (rs.next()) key=rs.getObject(1);
                rs.close();
                st.close();
                return key;

            }
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        } finally {
            close(con);
        }
    }

    public void clearCache(String ... table) {

    }


    /**
     * closes a connection. This method is Transaction-Safe.
     * @param con
     */
    public void close(Connection con) {
		try {
			con.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
    }

    /**
     * public void closes a Statemnt.
     * @param st
     */
    public void close(Statement st) {
    	try {
			st.close();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
    }



    // add the select
    private String fixSql(String sql) {
        if (!sql.startsWith("select") && !sql.startsWith("SELECT") && !sql.startsWith("(select")) {
            return "select * from ".concat(sql);
        }
        return sql;
    }


    private void setValuesInStatement(PreparedStatement st, Object... values)
            throws SQLException {
        for (int i=0;i<values.length;i++) {
            if (values[i]==null)
                st.setNull(i+1, Types.VARCHAR);
            else
                st.setObject(i+1, values[i]);
        }
    }


    /**
     * closes the DB-Connection.
     */
    public void close() {
        if (datasource!=null && datasource instanceof BasicDataSource) {
          try {
            ((BasicDataSource)datasource).close();
              datasource=null;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        }
        
        // remove from map
        if (instances!=null && id!=null)
          instances.remove(id);
    }

    private PreparedStatement prepareStatement(Connection con, String sql) {
    	try {
			return con.prepareStatement(sql);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
    }

	public String getId() {
		return id;
	}

}
